﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Collections;
using System.Data;
using System.Data.SQLite;


namespace RestaurantPicker
{
    class DataBaseConnection
    {
        private SQLiteConnection connection;
        private SQLiteCommand command;
        private DataSet dataSet = new DataSet();
        private DataTable dataTable = new DataTable();

        // Create connection to SQLite database
        public void OpenConnection()
        {
            // Create and open connection
            connection = new SQLiteConnection("Data Source=restaurantList.s3db;Version=3;New=False;Compress=True;");
            connection.Open();

            // create command and query
            command = connection.CreateCommand();
        }

        public void CloseConnection()
        {
            connection.Close();
        }

        public List<string> LoadList()
        {
            List<string> list = new List<string>();

            command.CommandText = "Select * FROM Restaurants";
            // Create a data reader and Execute the command
            SQLiteDataReader dataReader = command.ExecuteReader();

            // Read the data and store them in a list
            while (dataReader.Read())
            {
                list.Add(dataReader["RestaurantName"] + "");
            }

            return list;
        }

        // Execute the query that's passed as a parameter
        private void ExecuteQuery(string query)
        {
            command.CommandText = query;
            command.ExecuteNonQuery();
        }

        public void Add(string rest)
        {
            string restaurant = rest.Replace("\'", "\'\'");
            ExecuteQuery("INSERT INTO Restaurants VALUES ('" + restaurant + "');");
        }

        public void Delete(string rest)
        {
            string restaurant = rest.Replace("\'", "\'\'");
            ExecuteQuery("DELETE FROM Restaurants WHERE RestaurantName = '" + restaurant + "';");
        }
    }
}
